//  Copyright (c) 2010 Mahesh Sharma,Matt MacDonald
//
//  Permission is hereby granted, free of charge, to any person obtaining a copy
//  of this software and associated documentation files (the "Software"), to deal
//  in the Software without restriction, including without limitation the rights
//  to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
//  copies of the Software, and to permit persons to whom the Software is
//  furnished to do so, subject to the following conditions:
//
//  The above copyright notice and this permission notice shall be included in
//  all copies or substantial portions of the Software.
//
//  THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
//  IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
//  FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
//  AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
//  LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
//  OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
//  THE SOFTWARE.

package org.prx.prp.controller;

import java.util.ArrayList;

import org.prx.prp.model.Program;
import org.prx.prp.utility.DatabaseAdapter;

import android.content.ContentValues;
import android.database.Cursor;
import android.util.Log;

public class ProgramManager {

	private static ArrayList<Program> getFromDB(Cursor cursor) {
		ArrayList<Program> results = null;
		if (cursor != null) {
			cursor.moveToFirst();
			if (cursor.isAfterLast()) {
				cursor.close();
				return results;
			}
			try {
				results = new ArrayList<Program>(cursor.getCount());
				do {
					results.add(new Program(cursor));
				} while (cursor.moveToNext());
			} catch (Exception ex) {
				Log.d("PRPAND", ex.getMessage());
			}
		}
		cursor.close();
		return results;
	}

	public static ArrayList<Program> findAllPrograms() {

		Cursor cursor = DatabaseAdapter
				.executeQuery(
						"SELECT PROGRAMS.ID AS PROGRAM_ID, PROGRAMS.TITLE AS PROGRAM_TITLE, PROGRAMS.CATEGORY AS PROGRAM_CATEGORY, PROGRAMS.DESCRIPTION AS PROGRAM_DESCRIPTION, PROGRAMS.IMAGE_URL AS PROGRAM_IMAGE_URL, PROGRAMS.WEBSITE AS PROGRAM_WEBSITE, PROGRAMS.PODCAST AS PROGRAM_PODCAST FROM PROGRAMS WHERE PROGRAMS.PODCAST IS NOT NULL AND PROGRAMS.PODCAST != '' ORDER BY PROGRAMS.TITLE",
						null);
		return getFromDB(cursor);
	}

	public static ArrayList<Program> findAllProgramsByList(Integer listId) {
		Cursor cursor = DatabaseAdapter
				.executeQuery(
						"SELECT PROGRAMS.ID AS PROGRAM_ID, PROGRAMS.TITLE AS PROGRAM_TITLE, PROGRAMS.CATEGORY AS PROGRAM_CATEGORY, PROGRAMS.DESCRIPTION AS PROGRAM_DESCRIPTION, PROGRAMS.IMAGE_URL AS PROGRAM_IMAGE_URL, PROGRAMS.WEBSITE AS PROGRAM_WEBSITE, PROGRAMS.PODCAST AS PROGRAM_PODCAST FROM PROGRAMS JOIN PROGRAM_LISTINGS ON PROGRAM_LISTINGS.PROGRAM_LIST_ID = ? AND PROGRAM_LISTINGS.PROGRAM_ID = PROGRAMS.ID ORDER BY PROGRAMS.TITLE",
						new String[] { listId.toString() });
		return getFromDB(cursor);
	}

	public static Cursor findAllByCategory(Integer categoryId) {
		Cursor cursor = DatabaseAdapter
				.executeQuery(
						"SELECT PROGRAMS.ID AS PROGRAM_ID, PROGRAMS.TITLE AS PROGRAM_TITLE, PROGRAMS.CATEGORY AS PROGRAM_CATEGORY, PROGRAMS.DESCRIPTION AS PROGRAM_DESCRIPTION, PROGRAMS.IMAGE_URL AS PROGRAM_IMAGE_URL, PROGRAMS.WEBSITE AS PROGRAM_WEBSITE, PROGRAMS.PODCAST AS PROGRAM_PODCAST FROM PROGRAMS JOIN CATEGORIZATIONS ON CATEGORIZATIONS.PROGRAM_ID = PROGRAMS.ID AND CATEGORIZATIONS.CATEGORY_ID = ? WHERE PROGRAMS.PODCAST IS NOT NULL AND PROGRAMS.PODCAST != '' ORDER BY PROGRAMS.TITLE",
						new String[] { categoryId.toString() });
		return cursor;
	}

	public static Cursor findAllFavorites() {
		Cursor cursor = DatabaseAdapter
				.executeQuery(
						"SELECT PROGRAMS.ID AS PROGRAM_ID,PROGRAMS.ID as _id, PROGRAMS.TITLE AS PROGRAM_TITLE, PROGRAMS.CATEGORY AS PROGRAM_CATEGORY, PROGRAMS.DESCRIPTION AS PROGRAM_DESCRIPTION, PROGRAMS.IMAGE_URL AS PROGRAM_IMAGE_URL, PROGRAMS.WEBSITE AS PROGRAM_WEBSITE, PROGRAMS.PODCAST AS PROGRAM_PODCAST FROM PROGRAMS JOIN FAVORITE_PROGRAMS ON FAVORITE_PROGRAMS.ID = PROGRAMS.ID  WHERE PROGRAMS.PODCAST IS NOT NULL AND PROGRAMS.PODCAST != '' ORDER BY FAVORITE_PROGRAMS.POSITION",
						null);
		return cursor;
	}

	public static Program findById(Integer programId) {
		Cursor cursor = DatabaseAdapter
				.executeQuery(
						"SELECT PROGRAMS.ID AS PROGRAM_ID, PROGRAMS.TITLE AS PROGRAM_TITLE, PROGRAMS.CATEGORY AS PROGRAM_CATEGORY, PROGRAMS.DESCRIPTION AS PROGRAM_DESCRIPTION, PROGRAMS.IMAGE_URL AS PROGRAM_IMAGE_URL, PROGRAMS.WEBSITE AS PROGRAM_WEBSITE, PROGRAMS.PODCAST AS PROGRAM_PODCAST FROM PROGRAMS WHERE PROGRAMS.ID = ?",
						new String[] { programId.toString() });
		cursor.moveToFirst();
		Program program = new Program(cursor);
		cursor.close();
		return program;
	}

	public static void addFavorite(Integer programId) {
		Cursor cursor = DatabaseAdapter
				.executeQuery(
						"SELECT IFNULL(MAX(POSITION), -1) AS LAST_INDEX FROM FAVORITE_PROGRAMS",
						null);
		cursor.moveToFirst();
		int new_position = cursor.getInt(0) + 1;
		cursor.close();
		ContentValues params = new ContentValues();
		params.put("ID", programId);
		params.put("POSITION", new_position);
		DatabaseAdapter.insertRecord("FAVORITE_PROGRAMS", params);
	}

	public static void removeFavorite(Integer programId) {
		DatabaseAdapter.deleteRecord("FAVORITE_PROGRAMS",
				new String[] { "ID" }, new String[] { programId.toString() });
	}

	public static boolean isFavorite(Integer programId) {

		Cursor cursor = DatabaseAdapter
				.executeQuery(
						"SELECT COUNT(*) AS IS_FAVORITE FROM FAVORITE_PROGRAMS WHERE ID = ?",
						new String[] { programId.toString() });
		cursor.moveToFirst();
		boolean result;
		if (cursor.getCount() > 0) {
			int value = cursor.getInt(cursor.getColumnIndex("IS_FAVORITE"));
			result = (value > 0 ? true : false);
		} else
			result = false;
		cursor.close();
		return result;
	}

	public static void moveFavorite(Program program, Integer fromPosition,
			Integer toPosition) {
		if (fromPosition > toPosition) {
			Cursor cursor = DatabaseAdapter
					.executeQuery(
							"UPDATE FAVORITE_PROGRAMS SET POSITION = (POSITION + 1) WHERE POSITION < ? AND POSITION >= ?",
							new String[] { fromPosition.toString(),
									toPosition.toString() });
			cursor.close();

		} else if (fromPosition < toPosition) {
			Cursor cursor = DatabaseAdapter
					.executeQuery(
							"UPDATE FAVORITE_PROGRAMS SET POSITION = (POSITION - 1) WHERE POSITION > ? AND POSITION <= ?",
							new String[] { fromPosition.toString(),
									toPosition.toString() });
			cursor.close();
		}
		Cursor cursor = DatabaseAdapter.executeQuery(
				"UPDATE FAVORITE_PROGRAMS SET POSITION = ? WHERE ID = ?"

				, new String[] { toPosition.toString(),
						program.getProgramId().toString() });
		cursor.close();
	}

}
